#!/usr/bin/python
# -*- coding: utf-8 -*-
# googleearth as an GIS Desktop,my places as data folder, this program sync my places into postgresql.
# it will first look for postgis folder in my place,then, treat each subfold as database name,treat subsub
# folder as table name, treat Placemark name as table main key,and update all the Placemark into postgresql
# table, if some main key value is not exists in myplaces,delete it from the table.

# usage: myplace2pgsql [-d databasename] [-t tablename] [-k mainkeyname] [-f myplacefile]  

import sys,getopt,os
from xml.dom.minidom import parse
import pg

def usage():
    print '''usage:%s [-h] [-d databasename] [-t tablename] [-k mainkeyname] [-f myplacefile] -u username -p passwd
    -h: Show this information
    ''' % (sys.argv[0])
    exit(0)

userdatabase=""
usertable=""
usermainkey=""
myplacefile=os.path.expanduser('~/.googleearth/myplaces.kml')
pgconn=None
username=""
password=""

usernameReady=False
passwordReady=False
try:
    optlist, list = getopt.getopt(sys.argv[1:],'d:t:k:f:u:p:h')
    if len(list)>0:
        usage()
    for opt in optlist:
        if opt[0]=="-h":
            usage()
        elif opt[0]=="-d":
            userdatabase=opt[1]
        elif opt[0]=="-t":
            usertable=opt[1]
        elif opt[0]=="-k":
            usermainkey=opt[1]
        elif opt[0]=="-f":
            myplacefile=opt[1]
        elif opt[0]=="-u":
            username=opt[1]
            usernameReady=True
        elif opt[0]=="-p":
            password=opt[1]
            passwordReady=True
    if not usernameReady or not passwordReady:
        usage()
except getopt.GetoptError:
    usage()

def GeomSqlFromPlace(place):
    for geom in place.childNodes:
        execute=False
        if geom.nodeName=="LineString":
            ret='LINESTRING('
            end=')'
            execute=True
        elif geom.nodeName=="Polygon":
            ret='POLYGON(('
            end='))'
            execute=True
        if execute:
            start=True
            geomkml=geom.getElementsByTagName("coordinates")[0].childNodes[0].data
            for coordinate in geomkml.split(" "):
                if len(coordinate.strip())>0:
                    x,y,z=coordinate.split(",")
                    if start:
                        start=False
                    else:
                        ret+=","
                    ret+="%s %s" % (x,y);
            ret+=end
            return ret;

def ProcessPlace(place,databasename,tablename,placename):
    global pgconn
    sql="select * from %s where name = '%s'" % (tablename,placename)
    try:
        ds = pgconn.query(sql.encode("utf-8"))
        if len(ds.getresult())==0:
            sql="insert into %s (name,mark,geom) values ('%s',1,GeomFromText('%s',4326))" % (tablename,placename,GeomSqlFromPlace(place))
            pgconn.query(sql.encode("utf-8"))
        else:
            sql="update %s set mark=1,geom=GeomFromText('%s',4326) where name = '%s'" % (tablename,GeomSqlFromPlace(place),placename)
            pgconn.query(sql.encode("utf-8"))
    except Exception,e:
        print "执行SQL的时候失败:"+sql.encode("utf-8")
        print e

def MarkTable(databasename,tablename,table):
    global usermainkey,pgconn
    sql="select * from pg_class where relname='%s'" % (tablename)
    ds = pgconn.query(sql.encode("utf-8"))
    if len(ds.getresult())==0:#table does not exists,build the default one!
        geomStyle="LINESTRING"
        if len(table.getElementsByTagName("Polygon"))>0:
            geomStyle="POLYGON"
        print (u"创建新表:%s 的地理类型为：%s" % (tablename,geomStyle)).encode("utf-8")
        sql="CREATE TABLE %s (id serial NOT NULL PRIMARY KEY,name varchar(30) NOT NULL UNIQUE, mark integer);SELECT AddGeometryColumn('public', '%s', 'geom', 4326, '%s', 2);" % (tablename,tablename,geomStyle)
        pgconn.query(sql.encode("utf-8"))
    if usermainkey=="":#if update special record,no need do this
        sql="update %s set mark=0" % (tablename)
        pgconn.query(sql.encode("utf-8"))

def UnmarkTable(databasename,tablename):
    global usermainkey,pgconn
    if usermainkey=="":#if update special record,no need do this
        sql="delete from %s where mark=0" % (tablename)
        ds = pgconn.query(sql.encode("utf-8"))

def ProcessTable(table,databasename,tablename):
    MarkTable(databasename,tablename,table)
    for place in table.childNodes:
        if place.nodeName=="Placemark":
            placename=place.getElementsByTagName("name")[0].childNodes[0].data
            global usermainkey
            if usermainkey=="" or usermainkey==placename:
                ProcessPlace(place,databasename,tablename,placename)
            if usermainkey!="":
                break
    UnmarkTable(databasename,tablename)

def ProcessDatabase(database,databasename):
    global pgconn,username,password
    pgconn=pg.connect(databasename,'localhost',5432,None,None,username,password)
    for table in database.childNodes:
        if table.nodeName=="Folder":
            tablename=table.getElementsByTagName("name")[0].childNodes[0].data
            global usertable
            if usertable=="" or usertable==tablename:
                ProcessTable(table,databasename,tablename)

def ProcessPostgis(postgis):
    for database in postgis.childNodes:
        if database.nodeName=="Folder":
            databasename=database.getElementsByTagName("name")[0].childNodes[0].data
            global userdatabase
            if userdatabase=="" or userdatabase==databasename:
                ProcessDatabase(database,databasename)
    pass

#Let's parse the kml file first
dom1 = parse(myplacefile)
for child in dom1.documentElement.childNodes:
    if child.nodeName=="Document":
        for folder in child.childNodes:
            if folder.nodeName=="Folder":
                for postgis in folder.childNodes:
                    if postgis.nodeName=="Folder":
                        foldername=postgis.getElementsByTagName("name")
                        if foldername[0].childNodes[0].data=="postgis":
                            ProcessPostgis(postgis)
                            break
        break    
